﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
//using RCA1._0.DAL_WS;
using System.Text;
using System.Collections;
using RCA1._0;

namespace Royal_1._0_DAL_WS
{
    public class ChuongDAO: DataProvider
    {
        public DataTable LayBang()
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "select * from CHUONG";
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        #region "Lấy đối tượng"
        private ChuongDTO LayDoiTuong(String sql)
        {
            OleDbConnection cn = this.Connect();

            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();

            ChuongDTO tempDTO = null;
            if (dr.Read())
            {
                tempDTO = new ChuongDTO();
                tempDTO.IDChuong = Convert.ToInt32(dr["IDChuong"]);
                tempDTO.MaLoaiCongTy = Convert.ToInt32(dr["MaLoaiCongTy"]);
                tempDTO.TenChuong = dr["TenChuong"].ToString();
                tempDTO.SoChuong = dr["SoChuong"].ToString();
                
            }
            cn.Close();
            return tempDTO;
        }

        #endregion


        #region "Lấy danh sách"

        private List<ChuongDTO> LayDSach(String sql)
        {
            OleDbConnection cn = this.Connect();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            ChuongDTO tempDTO;
            List<ChuongDTO> dsCHUONG = new List<ChuongDTO>();
            while (dr.Read())
            {
                tempDTO = new ChuongDTO();
                tempDTO.IDChuong = Convert.ToInt32(dr["IDChuong"]);
                tempDTO.MaLoaiCongTy = Convert.ToInt32(dr["MaLoaiCongTy"]);
                tempDTO.TenChuong = dr["TenChuong"].ToString();
                tempDTO.SoChuong = dr["SoChuong"].ToString();
                dsCHUONG.Add(tempDTO);
            }
            return dsCHUONG;
        }

        public List<ChuongDTO> LayDanhSach()
        {
            String sql = "Select * From CHUONG ";
            return LayDSach(sql);
        }


        public List<ChuongDTO> LayDanhSachTheoMaLoaiCongTy(int MaLoaiCongTy)
        {
            String sql = "Select * From CHUONG Where MaLoaiCongTy = " + MaLoaiCongTy;
            return LayDSach(sql);
        }

        #endregion
        

        #region "Lấy danh sách tên chương"

        private List<ChuongDTO> LayDSachTen(String sql)
        {
            OleDbConnection cn = this.Connect();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            ChuongDTO tempDTO;
            List<ChuongDTO> dsCHUONG = new List<ChuongDTO>();
            while (dr.Read())
            {
                tempDTO = new ChuongDTO();
                tempDTO.TenChuong = dr["TenChuong"].ToString();
                dsCHUONG.Add(tempDTO);
            }
            return dsCHUONG;
        }

        public List<ChuongDTO> LayDanhSachTen()
        {
            String sql = "Select * From CHUONG ";
            return LayDSachTen(sql);
        }

        public List<ChuongDTO> LayDanhSachTenChuongTheoMaLoaiCongTy(int MaLoaiCongTy)
        {
            String sql = "Select * From CHUONG Where MaLoaiCongTy = " + MaLoaiCongTy;
            return LayDSachTen(sql);
        }

        #endregion


        #region "Lấy danh sách số chương"

        private List<ChuongDTO> LayDSachSoChuong(String sql)
        {
            OleDbConnection cn = this.Connect();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            ChuongDTO tempDTO;
            List<ChuongDTO> dsCHUONG = new List<ChuongDTO>();
            while (dr.Read())
            {
                tempDTO = new ChuongDTO();
                tempDTO.SoChuong = dr["SoChuong"].ToString();
                dsCHUONG.Add(tempDTO);
            }
            return dsCHUONG;
        }

        public List<ChuongDTO> LayDanhSachSoChuong()
        {
            String sql = "Select * From CHUONG ";
            return LayDSachSoChuong(sql);
        }

        public List<ChuongDTO> LayDanhSachSoChuongTheoMaLoaiCongTy(int MaLoaiCongTy)
        {
            String sql = "Select * From CHUONG Where MaLoaiCongTy = " + MaLoaiCongTy;
            return LayDSachSoChuong(sql);
        }

        #endregion
              

        #region "Tìm kiếm"

        public ChuongDTO TimTheoMa(int MaChuong)
        {

            string strSQL = "select * from CHUONG Where IDChuong=" + MaChuong.ToString();
            return LayDoiTuong(strSQL);
        }

        #endregion


        ///////////////
        //#region "Thêm"

        //public void Them(ChuongDTO tempDTO)
        //{
        //    OleDbConnection cn = this.Connect();
        //    string strSQL = "insert into CHUONG(TenChuong, SoChuong, IDCongTy) values (@TenChuong, @SoChuong, @IDCongTy)";
        //    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
        //    cmd.Parameters.Add("@TenChuong", OleDbType.LongVarWChar).Value = tempDTO.TenChuong;
        //    cmd.Parameters.Add("@SoChuong", OleDbType.LongVarWChar).Value = tempDTO.SoChuong;
        //    cmd.Parameters.Add("@IDCongTy", OleDbType.Integer).Value = tempDTO.IDCongTy;
        //    cmd.ExecuteNonQuery();
        //    cn.Close();
        //}

        //#endregion


        //#region "Xóa"

        //public void Xoa(int IDChuong)
        //{
        //    OleDbConnection cn = this.Connect();
        //    string strSQL = "delete from CHUONG Where IDChuong = " + IDChuong.ToString();
        //    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
        //    cmd.ExecuteNonQuery();
        //    cn.Close();
        //}

        //#endregion


        //#region "Sửa"

        //public void Sua(ChuongDTO tempDTO)
        //{
        //    OleDbConnection cn = this.Connect();
        //    string strSQL = "update CHUONG set TenChuong = @TenChuong, SoChuong = @SoChuong, IDCongTy =  @IDCongTy where IDChuong = @IDChuong";
        //    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
        //    cmd.Parameters.Add("@TenChuong", OleDbType.LongVarWChar).Value = tempDTO.TenChuong;
        //    cmd.Parameters.Add("@SoChuong", OleDbType.LongVarWChar).Value = tempDTO.SoChuong;
        //    cmd.Parameters.Add("@IDCongTy", OleDbType.Integer).Value = tempDTO.IDCongTy;
        //    cmd.Parameters.Add("@IDChuong", OleDbType.Integer).Value = tempDTO.IDChuong;
        //    cmd.ExecuteNonQuery();
        //    cn.Close();
        //}

        //#endregion

    }
}